﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using PPI.Client.Logic.XML;
using PPI.Client.Utils;
using PPI.Entities;
using PPI.Entities.Enumerator;
using PPI.Entities.Inspections;
using PPI.Entities.Lookup;
using PPI.Entities.Utility;
using PPI.Entities.XML;
using PPI.Mobile.Logic;
using VMind.Global;

namespace PPI.Client.Logic.Inspection
{
    public class LInspectionStructure
    {

        public static EInspectionStructure CreateByInspection(EInspection inspection )
        {
            inspection.Structure.Inspections.Remove(inspection);

            EInspectionStructure tmpInspectionStructure = new EInspectionStructure();
            tmpInspectionStructure.Contact = new EVendor_Contact(inspection.Contact.Id, inspection.Contact.Name);
            tmpInspectionStructure.ServerID = 1;
            tmpInspectionStructure.Altitude = inspection.Altitude;
            tmpInspectionStructure.Date = inspection.Date;
            tmpInspectionStructure.Handheld = inspection.Handheld;
            tmpInspectionStructure.Latitude = inspection.Latitude;
            tmpInspectionStructure.Longitude = inspection.Longitude;
            tmpInspectionStructure.Project = new EVendor_Project(inspection.Project.Id,inspection.Project.Name);
            tmpInspectionStructure.SatelliteCount = inspection.SatelliteCount;
            tmpInspectionStructure.SatelliteTime = inspection.SatelliteTime;
            tmpInspectionStructure.SolutionAvailable = inspection.SolutionAvailable;
            tmpInspectionStructure.Toptype = new EXMissionStructures_TopType(inspection.TopType.XMissionStructures_TopTypeId,inspection.TopType.Name);
            inspection.Structure = tmpInspectionStructure;
            tmpInspectionStructure.Inspections.Add(inspection);
            return tmpInspectionStructure;
        }

        public static void Save(EInspectionStructure structure,string connectionString)
        {
            StringBuilder _sql = new StringBuilder();
            List<SqlParameter> _parameters = new List<SqlParameter>();

            #region query

            _sql.AppendLine("Insert into tb_Vendor_InspVisit_XMissionStructures(");
            _sql.AppendLine(" Vendor_InspVisit_XMissionStructureId, ");
            _sql.AppendLine(" Vendor_ProjectFk, ");
            _sql.AppendLine(" Vendor_Visit_TypeFk, ");
            _sql.AppendLine(" Latitude, ");
            _sql.AppendLine(" Longitude, ");
            _sql.AppendLine(" Altitude, ");
            _sql.AppendLine(" SatelliteCount, ");
            _sql.AppendLine(" SatelliteTime, ");
            _sql.AppendLine(" SolutionAvailable, ");
            _sql.AppendLine(" Date, ");
            _sql.AppendLine(" Vendor_ContactFk, ");
            _sql.AppendLine(" XMissionStructures_TopTypeFk ");
            _sql.AppendLine(" )Values( ");
            _sql.AppendLine(" @Vendor_InspVisit_XMissionStructureId, ");
            _sql.AppendLine(" @Vendor_ProjectFk, ");
            _sql.AppendLine(" @Vendor_Visit_TypeFk, ");
            _sql.AppendLine(" @Latitude, ");
            _sql.AppendLine(" @Longitude, ");
            _sql.AppendLine(" @Altitude, ");
            _sql.AppendLine(" @SatelliteCount, ");
            _sql.AppendLine(" @SatelliteTime, ");
            _sql.AppendLine(" @SolutionAvailable, ");
            _sql.AppendLine(" @Date, ");
            _sql.AppendLine(" @Vendor_ContactFk, ");
            _sql.AppendLine(" @XMissionStructures_TopTypeFk ");
            _sql.AppendLine(" ) ");
            





            #endregion

            #region Parameters

            _parameters.Add(DBHelper.CreateIntParameter("@Vendor_InspVisit_XMissionStructureId", ParameterDirection.Input, false, structure.Id));
            _parameters.Add(DBHelper.CreateBitParameter("@SolutionAvailable", ParameterDirection.Input, false, structure.SolutionAvailable));

            #region Project
            if(structure.Project.Id != Int32.MinValue)
                _parameters.Add(DBHelper.CreateIntParameter("@Vendor_ProjectFk",ParameterDirection.Input,false,structure.Project.Id));
            else
                _parameters.Add(DBHelper.CreateIntParameter("@Vendor_ProjectFk", ParameterDirection.Input, true, DBNull.Value));

            #endregion

            #region Contact
            if (structure.Contact.Id != Int32.MinValue)
                _parameters.Add(DBHelper.CreateIntParameter("@Vendor_ContactFk", ParameterDirection.Input, false, structure.Contact.Id));
            else
                _parameters.Add(DBHelper.CreateIntParameter("@Vendor_ContactFk", ParameterDirection.Input, true, DBNull.Value));

            #endregion

            #region Visit Type
            if (structure.VisitType.Vendor_Visit_TypeId!= Int32.MinValue)
                _parameters.Add(DBHelper.CreateIntParameter("@Vendor_Visit_TypeFk", ParameterDirection.Input, false, structure.VisitType.Vendor_Visit_TypeId));
            else
                _parameters.Add(DBHelper.CreateIntParameter("@Vendor_Visit_TypeFk", ParameterDirection.Input, true, DBNull.Value));

            #endregion

            #region Latitude
            if (structure.Latitude!= double.MinValue)
                _parameters.Add(DBHelper.CreateFloatParameter("@Latitude", ParameterDirection.Input, false, structure.Latitude));
            else
                _parameters.Add(DBHelper.CreateFloatParameter("@Latitude", ParameterDirection.Input, true, DBNull.Value));

            #endregion

            #region Longitude
            if (structure.Longitude != double.MinValue)
                _parameters.Add(DBHelper.CreateFloatParameter("@Longitude", ParameterDirection.Input, false, structure.Longitude));
            else
                _parameters.Add(DBHelper.CreateFloatParameter("@Longitude", ParameterDirection.Input, true, DBNull.Value));

            #endregion

            #region Altitude
            if (structure.Altitude != double.MinValue)
                _parameters.Add(DBHelper.CreateFloatParameter("@Altitude", ParameterDirection.Input, false, structure.Altitude));
            else
                _parameters.Add(DBHelper.CreateFloatParameter("@Altitude", ParameterDirection.Input, true, DBNull.Value));

            #endregion

            #region SatelliteCount
            if (structure.SatelliteCount != Int32.MinValue)
                _parameters.Add(DBHelper.CreateIntParameter("@SatelliteCount", ParameterDirection.Input, false, structure.SatelliteCount));
            else
                _parameters.Add(DBHelper.CreateIntParameter("@SatelliteCount", ParameterDirection.Input, true, DBNull.Value));

            #endregion

            #region SatelliteTime
            if (structure.SatelliteTime != DateTime.MinValue)
                _parameters.Add(DBHelper.CreateIntParameter("@SatelliteTime", ParameterDirection.Input, false, structure.SatelliteTime));
            else
                _parameters.Add(DBHelper.CreateIntParameter("@SatelliteTime", ParameterDirection.Input, true, DBNull.Value));

            #endregion

            #region Date
            if (structure.Date != DateTime.MinValue)
                _parameters.Add(DBHelper.CreateIntParameter("@Date", ParameterDirection.Input, false, structure.Date));
            else
                _parameters.Add(DBHelper.CreateIntParameter("@Date", ParameterDirection.Input, true, DBNull.Value));

            #endregion

            #region Toptype
            if (structure.Toptype.XMissionStructures_TopTypeId != Int32.MinValue)
                _parameters.Add(DBHelper.CreateIntParameter("@XMissionStructures_TopTypeFk", ParameterDirection.Input, false, structure.Toptype.XMissionStructures_TopTypeId));
            else
                _parameters.Add(DBHelper.CreateIntParameter("@XMissionStructures_TopTypeFk", ParameterDirection.Input, true, DBNull.Value));

            #endregion



            



            #endregion

            try
            {
                LMobileDatabase.ExecuteSingleQueryWhithParameters(_sql.ToString(), connectionString, _parameters.ToArray());
            }
            catch(Exception ex)
            {
                RegisterError.SaveError(ex);
            }
        }

        public static List<EInspectionStructure> LoadProjectStructures(string connectionString)
        {
            return LoadProjectStructures(Int32.MinValue, connectionString);
        }


        public static List<EInspectionStructure> LoadProjectStructures(Int32 structureId, string connectionString)
        {
            StringBuilder sql = new StringBuilder();

            #region Query

            sql.AppendLine("Select ");

            //Pole Fields
            sql.AppendLine("P.UtilityInspectionId as Pole_Id, ");
            sql.AppendLine("P.Latitude as Pole_Latitude, ");
            sql.AppendLine("P.Longitude as Pole_Longitude, ");
            sql.AppendLine("P.Year as Pole_Year, ");
            sql.AppendLine("P.LVisit as Pole_LVisit, ");
            sql.AppendLine("P.Number as Pole_Number, ");
            sql.AppendLine("P.AcquisitionDate as Pole_AcquisitionDate, ");
            sql.AppendLine("P.GuardsQuantity,");

            sql.AppendLine("P.TreatmentFk as Pole_TreatmentFk, ");
            sql.AppendLine("TRE.LookupText as Pole_Treatment_Name, ");

            sql.AppendLine("P.ClassFk as Pole_ClassFk, ");
            sql.AppendLine("CLA.LookupText as Pole_Class_Name, ");

            sql.AppendLine("P.SpecieFk as Pole_SpecieFk, ");
            sql.AppendLine("E.LookupText as Pole_Specie_Name, ");

            sql.AppendLine("P.TopTypeFk as Pole_TopTypeFk, ");
            sql.AppendLine("T.Name as Pole_TopType_Name, ");

            sql.AppendLine("P.LengthFk as Pole_LengthFk, ");
            sql.AppendLine("L.Name as Pole_Length_Name, ");

            sql.AppendLine("P.InspectorFk as Pole_InspectorFk, ");
            sql.AppendLine("INSPEC.Name as Pole_Inspector_Name, ");

            sql.AppendLine("P.CompanyFk as Pole_CompanyFk, ");
            sql.AppendLine("C.Name as Pole_Company_Name, ");

            sql.AppendLine("P.MapFk as Pole_MapFk, ");
            sql.AppendLine("MA.Name as Pole_Map_Name, ");
            sql.AppendLine("P.SectionFk as Pole_Section, ");

            sql.AppendLine("P.ProjectFk as Pole_ProjectFk, ");
            sql.AppendLine("PR.Name as Pole_Project_Name, ");

            sql.AppendLine("P.ContactFk as Pole_ContactFk, ");
            sql.AppendLine("P.GuardsQuantity, ");
            sql.AppendLine("P.LastCompany, ");
            sql.AppendLine("US.Name as Pole_Contact_Name, ");

            sql.AppendLine("V.VendorId as Pole_VendorFk, ");
            sql.AppendLine("V.Name as Pole_Vendor_Name, ");

            sql.AppendLine("P.Vendor_InspVisit_XMissionStructureFk as Pole_StructureFk, ");

            //Result Fields
            sql.AppendLine("R.UtilityInspectionResultId as Result_Id, ");
            sql.AppendLine("R.UtilityInspectionFk as Result_InspectionTypeFk, ");
            sql.AppendLine("I.Name as Result_InspectionType_Name, ");
            sql.AppendLine("R.InspectionTypeFk, ");
            sql.AppendLine("R.ResultTypeFk as Result_TypeFk, ");
            sql.AppendLine("Re.Name as Result_Type_Name, ");
            sql.AppendLine("R.Comments as Result_Comments, ");
            sql.AppendLine("R.bElectrical as Result_IsElectrical, ");

            //Structure Fields
            sql.AppendLine("S.Vendor_InspVisit_XMissionStructureId as Structure_Id, ");

            sql.AppendLine("V2.VendorId as Structure_VendorFk, ");
            sql.AppendLine("V2.Name as Structure_Vendor_Name, ");

            sql.AppendLine("S.Vendor_ProjectFk as Structure_ProjectFk, ");
            sql.AppendLine("PR2.Name as Structure_Project_Name, ");

            sql.AppendLine("S.Utility_Asset_XMisssionStructureFk as Structure_UtilityFk, ");

            sql.AppendLine("S.Vendor_Visit_TypeFK as Structure_VisitTypeFk, ");
            sql.AppendLine("VType.LookupText as Structure_VisitType_Name, ");


            sql.AppendLine("S.Latitude as Structure_Latitude, ");
            sql.AppendLine("S.Longitude as Structure_Longitude, ");
            sql.AppendLine("S.Altitude as Structure_Altitude, ");
            sql.AppendLine("S.SatelliteCount as Structure_SatelliteCount, ");
            sql.AppendLine("S.SatelliteTime as Structure_SatelliteTime, ");
            sql.AppendLine("S.SolutionAvailable as Structure_SolutionAvailable, ");

            sql.AppendLine("S.Vendor_ContactFk as Structure_ContactFk, ");
            sql.AppendLine("US2.Name as Structure_Contact_Name, ");

            sql.AppendLine("S.Date as Structure_Date, ");
            sql.AppendLine("S.HandheldFK as Structure_HandheldFk, ");

            sql.AppendLine("S.XMissionStructures_TopTypeFk as Structure_TopTypeFk, ");
            sql.AppendLine("T2.Name as Structure_TopType_Name, ");
            sql.AppendLine("P.LastCompany,  ");
            sql.AppendLine("P.VisitTypeFk, ");
            sql.AppendLine("VT.LookupText as VisitType_Name  ");

            sql.AppendLine("From tb_UtilityInspections as P");
            sql.AppendLine("    INNER JOIN tb_UtilityInspectionResults R ON P.UtilityInspectionId = R.UtilityInspectionFk");
            sql.AppendLine("    LEFT JOIN tb_Vendor_InspVisit_XMissionStructures S ON P.Vendor_InspVisit_XMissionStructureFk = S.Vendor_InspVisit_XMissionStructureId");
            sql.AppendLine("    LEFT JOIN tb_InspectionTypes I ON R.InspectionTypeFk = I.InspectionTypeId");
            sql.AppendLine("    LEFT JOIN tb_ResultTypes Re ON R.ResultTypeFk= Re.ResultTypeId ");

            sql.AppendLine("    LEFT JOIN tb_Tops T ON P.ToptypeFk = T.TopId");
            sql.AppendLine("    LEFT JOIN tb_Tops T2 ON S.XMissionStructures_TopTypeFk = T2.TopId");
            sql.AppendLine("    LEFT JOIN tb_Companies C ON P.CompanyFk = C.CompanyId");
            sql.AppendLine("    LEFT JOIN tb_Inspectors INSPEC ON P.InspectorFk = INSPEC.InspectorId");
            sql.AppendLine("    LEFT JOIN tb_Lookups TRE ON (P.TreatmentFk = TRE.LookupValue AND TRE.LookupTypeFk = 'Treatments' )");
            sql.AppendLine("    LEFT JOIN tb_Lookups CLA ON (P.ClassFk = CLA.LookupValue AND CLA.LookupTypeFk = 'Classes' )");
            sql.AppendLine("    LEFT JOIN tb_Lookups E ON (P.SpecieFk = E.LookupValue AND E.LookupTypeFk = 'Species' )");
            sql.AppendLine("    LEFT JOIN tb_Maps MA ON P.MapFk = MA.MapId ");
            sql.AppendLine("    LEFT JOIN tb_Users US ON P.ContactFk = US.UserId ");
            sql.AppendLine("    LEFT JOIN tb_Users US2 ON S.Vendor_ContactFk = US2.UserId ");
            sql.AppendLine("    LEFT JOIN tb_Projects PR ON P.ProjectFk = PR.ProjectId ");
            sql.AppendLine("    LEFT JOIN tb_Projects PR2 ON S.Vendor_ProjectFk = PR2.ProjectId ");
            sql.AppendLine("    LEFT JOIN tb_Vendor V ON PR.VendorFk = V.VendorId ");
            sql.AppendLine("    LEFT JOIN tb_Vendor V2 ON PR2.VendorFk = V2.VendorId ");
            sql.AppendLine("    LEFT JOIN tb_Lookups as VType ON VType.LookupValue = S.Vendor_Visit_TypeFk AND VType.LookupTypeFk ='VisitTypes'");
            sql.AppendLine("    LEFT JOIN tb_Lookups as VT ON VT.LookupValue = P.VisitTypeFk AND VT.LookupTypeFk ='VisitTypes'");
            sql.AppendLine("    LEFT JOIN tb_Lengths L ON P.LengthFk = L.LengthId");
            sql.AppendLine(" Where (@StructureId is null OR S.Vendor_InspVisit_XMissionStructureId = @StructureId)");
            sql.AppendLine("Order by Vendor_InspVisit_XMissionStructureId");

            #endregion

            List<SqlParameter> listParameters = new List<SqlParameter>();
            if (structureId == Int32.MinValue)
                listParameters.Add(DBHelper.CreateIntParameter("@StructureId", ParameterDirection.Input, true, DBNull.Value));
            else
                listParameters.Add(DBHelper.CreateIntParameter("@StructureId", ParameterDirection.Input, false, structureId));

            DataTable dtStructures = LMobileDatabase.ExecuteQueryDataset(sql.ToString(), connectionString, listParameters.ToArray()).Tables[0];

            List<EInspectionStructure> structures = ParseDatatableStructures(dtStructures, connectionString);
            return structures;

        }

        /// <summary>
        /// Transform datatable to a list of Inspection Structure Entites
        /// </summary>
        /// <param name="structures"></param>
        /// <param name="connectionString"></param>
        /// <returns></returns>
        /// <history>
        ///     WB 1/31/2009 - Added Calls to retrieve ALL Notes and and Images outside of Loop. Utilized LINQ to populate inspection
        ///                    notes and images so that every record does not query database unless method is called after retrieval
        ///                    of only a single structure. Massive performance increase.
        /// </history>
        private static List<EInspectionStructure> ParseDatatableStructures(DataTable structures, string connectionString)
        {

            List<EInspectionStructure> Liststructures = new List<EInspectionStructure>();
            List<EInspection> listInspections = new List<EInspection>();

            //determine if there is a single structure or many
            bool _SingleStructure = false;
            var _CountStructureIds = from structure in structures.AsEnumerable()
                                     select structure.Field<int?>("Structure_Id");

            if (_CountStructureIds.Distinct().Count() <= 1)
                _SingleStructure = true;


            List<ENote> _ListNotes = null;
            List<EInspectionImage> _ListImages = null;

            //retrieve all notes and images from the mobile database
            if (!_SingleStructure)
            {
                _ListNotes = LNote.GetMobileAll(connectionString);
                _ListImages = LInspectionImage.GetAll(connectionString);
            }

            Int32 structureIdFlag = 0;
            for (int i = 0; i < structures.Rows.Count; i++ )
            {
                DataRow dr = structures.Rows[i];
                Int32 structureId = ConvertHelper.ToInt32(dr["Structure_Id"]);
                if (structureIdFlag != 0)
                {
                    if (structureIdFlag != structureId)
                    {
                        DataRow pastdr = structures.Rows[i -1];
                        structureIdFlag = structureId;
                        EInspectionStructure tmpInspectionStructure = new EInspectionStructure();
                        tmpInspectionStructure.UtilityAsset = new EUtility_Asset_XMissionStructure(ConvertHelper.ToInt32(pastdr["Structure_UtilityFk"]));
                        tmpInspectionStructure.Altitude = ConvertHelper.Todouble(pastdr["Structure_Altitude"]);
                        tmpInspectionStructure.Longitude = ConvertHelper.Todouble(pastdr["Structure_Longitude"]);
                        tmpInspectionStructure.Latitude = ConvertHelper.Todouble(pastdr["Structure_Latitude"]);
                        tmpInspectionStructure.Contact = new EVendor_Contact(ConvertHelper.ToInt32(pastdr["Structure_ContactFk"]), pastdr["Structure_Contact_Name"].ToString());
                        tmpInspectionStructure.Contact.Vendor = new EVendor(ConvertHelper.ToInt32(pastdr["Structure_VendorFk"]), pastdr["Structure_Vendor_Name"].ToString(), "");
                        tmpInspectionStructure.Project = new EVendor_Project(ConvertHelper.ToInt32(pastdr["Structure_ProjectFk"]), pastdr["Structure_Project_Name"].ToString());
                        tmpInspectionStructure.Project.Vendor = new EVendor(ConvertHelper.ToInt32(pastdr["Structure_VendorFk"]), pastdr["Structure_Vendor_Name"].ToString(), "");
                        tmpInspectionStructure.Date = ConvertHelper.ToDateTime(pastdr["Structure_Date"]);
                        tmpInspectionStructure.Handheld = new EHandheld(ConvertHelper.ToInt32(pastdr["Structure_handheldFk"]), "");
                        tmpInspectionStructure.Id = ConvertHelper.ToInt32(pastdr["Structure_Id"]);
                        tmpInspectionStructure.SatelliteCount = ConvertHelper.ToInt32(pastdr["Structure_SatelliteCount"]);
                        tmpInspectionStructure.SatelliteTime = ConvertHelper.ToDateTime(pastdr["Structure_SatelliteTime"]);
                        tmpInspectionStructure.ServerID = tmpInspectionStructure.Id;
                        tmpInspectionStructure.SolutionAvailable = ConvertHelper.Tobool(pastdr["Structure_SolutionAvailable"]);
                        tmpInspectionStructure.VisitType = new EVendor_Visit_Type(ConvertHelper.ToInt32(pastdr["Structure_VisitTypeFk"]), pastdr["Structure_VisitType_Name"].ToString());
                        tmpInspectionStructure.Toptype = new EXMissionStructures_TopType(ConvertHelper.ToInt32(pastdr["Structure_TopTypeFk"]), pastdr["Structure_TopType_Name"].ToString());
                        tmpInspectionStructure.Inspections = listInspections;
                        foreach (EInspection inspection in listInspections)
                        {
                            inspection.Structure = tmpInspectionStructure;
                        }
                        Liststructures.Add(tmpInspectionStructure);
                        listInspections = new List<EInspection>();

                    }
                }
                else
                {
                    structureIdFlag = structureId;
                }


                EInspectionResult tmpInspectionResult = new EInspectionResult();
                tmpInspectionResult.Id = ConvertHelper.ToInt32(dr["Result_Id"]);
                tmpInspectionResult.SeverId = tmpInspectionResult.Id;
                tmpInspectionResult.InspectionType = new EInspectionTypes(ConvertHelper.ToInt32(dr["InspectionTypeFk"]), ConvertHelper.Tostring(dr["Result_InspectionType_Name"]));
                tmpInspectionResult.Resultype = new EResultType(ConvertHelper.ToInt32(dr["Result_TypeFk"]), ConvertHelper.Tostring(dr["Result_Type_Name"]));

                //WB 1/31/2009 - remove query for each record unless its a single structure
                //tmpInspectionResult.Notes = LNote.GetMobileByResultId(connectionString, tmpInspectionResult.Id);

                if (_SingleStructure)
                {
                    tmpInspectionResult.Notes = LNote.GetMobileByResultId(connectionString, tmpInspectionResult.Id);
                }
                else
                {
                    //get the corresponding notes for the inspection
                    var _InspectionNotes = from notes in _ListNotes
                                           where notes.ResultId.HasValue && notes.ResultId.Value == tmpInspectionResult.Id
                                           select notes;
                    //populate the Notes property with the LINQ result
                    tmpInspectionResult.Notes = _InspectionNotes.ToList();
                }
                
                tmpInspectionResult.Comments = dr["Result_Comments"].ToString();
                tmpInspectionResult.IsElectrical = ConvertHelper.Tobool(dr["Result_IsElectrical"]);


                EInspection tmpInspection = new EInspection();
                tmpInspection.DatabaseType = DatabaseTypes.Project;
                tmpInspection.ServerID = ConvertHelper.ToInt32(dr["Pole_Id"]);
                tmpInspection.AcquisitionDate = ConvertHelper.ToDateTime(dr["Pole_AcquisitionDate"]);
                tmpInspection.Class = new EXMissionStructures_PoleClass(ConvertHelper.ToInt32(dr["Pole_ClassFK"]), ConvertHelper.Tostring(dr["Pole_Class_Name"]));
                tmpInspection.Company = new ECompany(ConvertHelper.ToInt32(dr["Pole_CompanyFK"]), ConvertHelper.Tostring(dr["Pole_Company_Name"]));
                tmpInspection.Id = ConvertHelper.ToInt32(dr["Pole_Id"]);
                tmpInspection.Inspector = new EInspector(ConvertHelper.ToInt32(dr["Pole_InspectorFk"]), ConvertHelper.Tostring(dr["Pole_Inspector_Name"]));
                tmpInspection.Latitude = ConvertHelper.Todouble(dr["Pole_Latitude"]);
                tmpInspection.Longitude = ConvertHelper.Todouble(dr["Pole_Longitude"]);
                tmpInspection.Length = ConvertHelper.ToInt32(dr["Pole_Length_Name"]);
                tmpInspection.LastVisit = ConvertHelper.ToDateTime(dr["Pole_LVisit"]);
                tmpInspection.Map = new EMap(ConvertHelper.ToInt32(dr["Pole_MapFk"]), ConvertHelper.Tostring(dr["Pole_Map_Name"]));
                tmpInspection.Section = new ESection(ConvertHelper.ToInt32(dr["Pole_Section"]), dr["Pole_Section"].ToString());
                tmpInspection.Number = ConvertHelper.Tostring(dr["Pole_Number"]);
                tmpInspection.Specie = new EXMissionStructures_PoleSpecies(ConvertHelper.ToInt32(dr["Pole_SpecieFk"]), ConvertHelper.Tostring(dr["Pole_Specie_Name"]));
                tmpInspection.TopType = new EXMissionStructures_TopType(ConvertHelper.ToInt32(dr["Pole_TopTypeFk"]), ConvertHelper.Tostring(dr["Pole_TopType_Name"]));
                tmpInspection.Treatment = new EXMissionStructures_PoleTreatment(ConvertHelper.ToInt32(dr["Pole_TreatmentFk"]), ConvertHelper.Tostring(dr["Pole_Treatment_Name"]));
                tmpInspection.Year = ConvertHelper.ToInt32(dr["Pole_Year"]);
                tmpInspection.GuardsQuantity = dr["GuardsQuantity"] != null ? ConvertHelper.ToInt32(dr["GuardsQuantity"]) : Int32.MinValue;
                tmpInspection.LastCompanyName = dr["LastCompany"] != null ? ConvertHelper.Tostring(dr["LastCompany"]) : string.Empty;
                tmpInspection.VendorVisitType = new EVendor_Visit_Type(ConvertHelper.ToInt32(dr["VisitTypeFk"]), ConvertHelper.Tostring(dr["VisitType_Name"]));

                //WB 1/31/2009 - remove query for each record unless its a single structure
                //tmpInspection.Images = LInspectionImage.GetByInspection(connectionString, tmpInspection.Id);
                if (_SingleStructure)
                {
                    tmpInspection.Images = LInspectionImage.GetByInspection(connectionString, tmpInspection.Id);
                }
                else
                {
                    //get the corresponding images for the inspection
                    var _InspectionImages = from images in _ListImages
                                            where images.Inspection.Id == tmpInspection.Id
                                            select images;

                    //populate the Images property with the LINQ result
                    tmpInspection.Images = _InspectionImages.ToList();
                }
                
                tmpInspection.Contact = new EVendor_Contact(ConvertHelper.ToInt32(dr["Pole_ContactFk"]), dr["Pole_Contact_Name"].ToString());
                tmpInspection.Contact.Vendor = new EVendor(ConvertHelper.ToInt32(dr["Pole_VendorFk"]), dr["Pole_Vendor_Name"].ToString(), "");
                tmpInspection.Project = new EVendor_Project(ConvertHelper.ToInt32(dr["Pole_ProjectFk"]), dr["Pole_Project_Name"].ToString());
                tmpInspection.Project.Vendor = new EVendor(ConvertHelper.ToInt32(dr["Pole_VendorFk"]), dr["Pole_Vendor_Name"].ToString(), "");
                tmpInspection.Structure = new EInspectionStructure(ConvertHelper.ToInt32(dr["Pole_StructureFk"]));
                tmpInspection.Result = tmpInspectionResult;
                listInspections.Add(tmpInspection);
            }

            DataRow lastdr = structures.Rows[structures.Rows.Count - 1];
            EInspectionStructure lastInspectionStructure = new EInspectionStructure();
            lastInspectionStructure.UtilityAsset = new EUtility_Asset_XMissionStructure(ConvertHelper.ToInt32(lastdr["Structure_UtilityFk"]));
            lastInspectionStructure.Altitude = ConvertHelper.Todouble(lastdr["Structure_Altitude"]);
            lastInspectionStructure.Longitude = ConvertHelper.Todouble(lastdr["Structure_Longitude"]);
            lastInspectionStructure.Latitude = ConvertHelper.Todouble(lastdr["Structure_Latitude"]);
            lastInspectionStructure.Contact = new EVendor_Contact(ConvertHelper.ToInt32(lastdr["Structure_ContactFk"]), lastdr["Structure_Contact_Name"].ToString());
            lastInspectionStructure.Contact.Vendor = new EVendor(ConvertHelper.ToInt32(lastdr["Structure_VendorFk"]), lastdr["Structure_Vendor_Name"].ToString(), "");
            lastInspectionStructure.Project = new EVendor_Project(ConvertHelper.ToInt32(lastdr["Structure_ProjectFk"]), lastdr["Structure_Project_Name"].ToString());
            lastInspectionStructure.Project.Vendor = new EVendor(ConvertHelper.ToInt32(lastdr["Structure_VendorFk"]), lastdr["Structure_Vendor_Name"].ToString(), "");
            lastInspectionStructure.Date = ConvertHelper.ToDateTime(lastdr["Structure_Date"]);
            lastInspectionStructure.Handheld = new EHandheld(ConvertHelper.ToInt32(lastdr["Structure_handheldFk"]), "");
            lastInspectionStructure.Id = ConvertHelper.ToInt32(lastdr["Structure_Id"]);
            lastInspectionStructure.SatelliteCount = ConvertHelper.ToInt32(lastdr["Structure_SatelliteCount"]);
            lastInspectionStructure.SatelliteTime = ConvertHelper.ToDateTime(lastdr["Structure_SatelliteTime"]);
            lastInspectionStructure.ServerID = lastInspectionStructure.Id;
            lastInspectionStructure.SolutionAvailable = ConvertHelper.Tobool(lastdr["Structure_SolutionAvailable"]);
            lastInspectionStructure.VisitType = new EVendor_Visit_Type(ConvertHelper.ToInt32(lastdr["Structure_VisitTypeFk"]), lastdr["Structure_VisitType_Name"].ToString());
            lastInspectionStructure.Toptype = new EXMissionStructures_TopType(ConvertHelper.ToInt32(lastdr["Structure_TopTypeFk"]), lastdr["Structure_TopType_Name"].ToString());
            lastInspectionStructure.Inspections = listInspections;
            foreach (EInspection inspection in listInspections)
            {
                inspection.Structure = lastInspectionStructure;
            }
            Liststructures.Add(lastInspectionStructure);

            return Liststructures;
        }

        public static List<EInspectionStructure> Autodetermine(List<EInspectionStructure> structures,  Int32 meters, string connectionString)
        {

            List<EInspection> inspectionsNotAssigned = new List<EInspection>();
            List<EInspection> inspectionWithStructures = new List<EInspection>();
            List<EInspection> inspectionWithNewStructures = new List<EInspection>();


            EInspectionStructure structureNotAssigned = structures.Find(NotAssignedPredicate);
            if (structureNotAssigned != null)
                inspectionsNotAssigned = structureNotAssigned.Inspections.FindAll(
                    delegate(EInspection i)
                        {
                            return
                                i.Latitude != 0 && i.Longitude != 0 &&
                                i.Latitude != double.MinValue && i.Longitude != double.MinValue;
                        });

            if (inspectionsNotAssigned.Count > 0)
            {
                foreach (EInspectionStructure structure in structures)
                {
                    if(structure.Id != Int32.MinValue)
                    {
                        foreach (EInspection inspection in inspectionsNotAssigned)
                        {
                            //verifico si la estructura actual no podria contener la inspection
                            if (GeoHelper.GetDistanceBetweenCoordinates(inspection.Latitude, inspection.Longitude, structure.Latitude, structure.Longitude) <= meters)
                            {
                                inspection.Structure = structure;
                                inspectionWithNewStructures.Add(inspection);
                            }
                            else
                            {
                                //verifico si algun pole de la estructura actual esta cerca de la inspection
                                EInspection tmpInspection = structure.Inspections.Find(
                                    delegate(EInspection i)
                                    {
                                        return GeoHelper.GetDistanceBetweenCoordinates(inspection.Latitude, inspection.Longitude, i.Latitude, i.Longitude) <= meters;
                                    });
                                if (tmpInspection != null)
                                {
                                    inspection.Structure = structure;
                                    inspectionWithNewStructures.Add(inspection);
                                }
                            }
                        }
                    }
                    
                }

                for (Int32 i = 0; i < inspectionsNotAssigned.Count; i++ )
                {
                    if (!inspectionWithNewStructures.Contains(inspectionsNotAssigned[i]))
                    {
                        EInspectionStructure tmpStructure = CreateByInspection(inspectionsNotAssigned[i]);
                        tmpStructure.Id = LInspection.CreateVisit(inspectionsNotAssigned[i], tmpStructure.Inspections);
                        LInspectionStructure.Save(tmpStructure, connectionString);
                        foreach (EInspection inspection in tmpStructure.Inspections)
                        {
                            LInspection.SetStructureMobile(inspection, tmpStructure, connectionString);
                            LInspection.SetVisit(inspection, tmpStructure.Id);
                        }
                        structures.Add(tmpStructure);
                    }
                    else
                    {
                        LInspection.SetVisit(inspectionsNotAssigned[i], inspectionsNotAssigned[i].Structure.Id);
                        LInspection.SetStructureMobile(inspectionsNotAssigned[i], inspectionsNotAssigned[i].Structure, connectionString);
                    }
                }


                for (Int32 i = 0; i < inspectionsNotAssigned.Count; i++ )
                {
                    structureNotAssigned.Inspections.Remove(inspectionsNotAssigned[i]);
                }
                Autodetermine(structures, meters, connectionString);
            }

            //foreach (EInspectionStructure structure in structures)
            //{
            //    foreach (EInspection i in structure.Inspections)
            //    {
            //        if (i.Structure.ServerID > -1)
            //            inspectionWithStructures.Add(i);
            //        else 
            //            inspectionsNotAssigned.Add(i);

            //    }
            //}

            //if(inspectionsNotAssigned.Count > 0)
            //{
            //    //verifico si la primer inspection de la lista se la puede asignar a alguna structura
            //    EInspection tmpInspection = inspectionsNotAssigned[0];
            //    bool isAdded = false;
            //    foreach (EInspectionStructure structure in structures)
            //    {
            //        if (GeoHelper.GetDistanceBetweenCoordinates(tmpInspection.Latitude, tmpInspection.Longitude, structure.Latitude, structure.Longitude) <= meters)
            //        {
            //            if (!structure.Inspections.Contains(tmpInspection))
            //            {
            //                isAdded = true;
            //                tmpInspection.Structure.Inspections.Remove(tmpInspection); //quito la inspection de la vieja structura
            //                structure.Inspections.Add(tmpInspection);
            //                tmpInspection.Structure = structure;
            //            }
            //        }
            //    }

            //    //verifico si la primer inspection de la lista se la puede asignar a alguna structura de alguna inspection
            //    if (!isAdded)
            //    {
            //        for (int i = 0; i < inspectionWithStructures.Count; i++)
            //        {

            //            if (GeoHelper.GetDistanceBetweenCoordinates(inspectionWithStructures[i].Latitude, inspectionWithStructures[i].Longitude, tmpInspection.Latitude, tmpInspection.Longitude) <= meters)
            //            {
            //                if (!inspectionWithStructures[i].Structure.Inspections.Contains(tmpInspection))
            //                {
            //                    isAdded = true;
            //                    tmpInspection.Structure.Inspections.Remove(tmpInspection);
            //                    inspectionWithStructures[i].Structure.Inspections.Add(tmpInspection);
            //                    tmpInspection.Structure = inspectionWithStructures[i].Structure;
            //                }
            //            }
            //        }
            //    }

            //    //si no la asigne a nadie, creo una structura con la inspection
            //    if (!isAdded)
            //    {
            //        EInspectionStructure tmpStructure = LInspectionStructure.CreateByInspection(tmpInspection);
            //        structures.Add(tmpStructure);
            //    }
            //    Autodetermine(structures, meters,connectionString);
            //}else
            //{
            //    foreach (EInspectionStructure structure in structures)
            //    {
            //        if(structure.Inspections.Count > 0)
            //        {
            //            try
            //            {
            //                if (structure.Id == Int32.MinValue)
            //                {
            //                    structure.Id = LInspection.CreateVisit(structure.Inspections[0], structure.Inspections);
            //                    LInspectionStructure.Save(structure, connectionString);
            //                    foreach (EInspection inspection in structure.Inspections)
            //                    {
            //                        LInspection.SetStructureMobile(inspection, structure, connectionString);
            //                    }
            //                }
                            
            //                else
            //                {
            //                    foreach (EInspection inspection in structure.Inspections)
            //                    {
            //                        LInspection.SetVisit(inspection, structure.Id);
            //                        LInspection.SetStructureMobile(inspection, structure,connectionString);
            //                    }
            //                }
            //            }
            //            catch (Exception ex)
            //            {
            //                RegisterError.SaveError(ex);
            //            }
                       
            //        }
            //    }
            //}

            return structures;
        }

        public static List<EInspectionStructure> FindInpectionsBetweenCoordinates(List<EInspectionStructure> structures, double maxLongitude, double maxLatitude, double minLongitude, double minLatiude)
        {
            List<EInspectionStructure> returnStructures = new List<EInspectionStructure>();
            EInspectionStructure tmpStructuresToInspectionsDisassociated = new EInspectionStructure();
            EInspectionStructure tmpStructure = new EInspectionStructure();
            List<EInspection> tmpListInspections = new List<EInspection>();

            foreach (EInspectionStructure s in structures)
            {
                tmpListInspections = s.Inspections.FindAll(delegate(EInspection i) { return i.Latitude <= maxLatitude && i.Latitude >= minLatiude && i.Longitude >= minLongitude && i.Longitude <= maxLongitude; });
                //foreach (EInspection inspect in s.Inspections)
                //{
                //    if (inspect.Latitude <= maxLatitude && inspect.Latitude >= minLatiude && inspect.Longitude >= minLongitude && inspect.Longitude <= maxLongitude)
                //        tmpStructure.Inspections.Add(inspect);
                //}
                tmpStructure = (EInspectionStructure)s.Clone();
                tmpStructure.Inspections = tmpListInspections;
                returnStructures.Add(tmpStructure);

                //if(s.Id == Int32.MinValue)
                //{
                //    foreach (EInspection inspect in s.Inspections)
                //    {
                //        if (inspect.Latitude <= maxLatitude && inspect.Latitude >= minLatiude && inspect.Longitude >= minLongitude && inspect.Longitude <= maxLongitude)
                //            tmpStructuresToInspectionsDisassociated.Inspections.Add(inspect);
                //    }
                //    returnStructures.Add(tmpStructuresToInspectionsDisassociated);
                //}
                //if (s.Latitude <= maxLatitude && s.Latitude >= minLatiude && s.Longitude >= minLongitude && s.Longitude <= maxLongitude)
                //    returnStructures.Add(s);
            }
            return returnStructures;


        }

        public static List<EInspectionStructure> MatchingVendors(List<EInspectionStructure> structures, List<EVendor> vendors)
        {
            List<EInspectionStructure> matchingVisits = new List<EInspectionStructure>();
            foreach (EInspectionStructure s in structures)
            {
                foreach (EVendor vendor in vendors)
                {
                    if (s.Project.Vendor.Id == vendor.Id)
                        matchingVisits.Add(s);
                }
            }
            return matchingVisits;
        }

        public static List<EInspectionStructure> MatchingContacts(List<EInspectionStructure> structures, List<EVendor_Contact> contacts)
        {
            List<EInspectionStructure> matchingVisits = new List<EInspectionStructure>();
            foreach (EInspectionStructure s in structures)
            {
                foreach (EVendor_Contact contact in contacts)
                {
                    if (s.Contact.Id == contact.Id)
                        matchingVisits.Add(s);
                }
            }
            return matchingVisits;
        }

        public static List<EInspectionStructure> MatchingInspectionType(List<EInspectionStructure> structures, List<EInspectionTypes> types)
        {
            List<EInspectionStructure> matchingVisits = new List<EInspectionStructure>();
            List<EInspection> tmpInspections;
            EInspectionStructure tmpInspectionStructure;
            foreach (EInspectionStructure s in structures)
            {
                foreach (EInspectionTypes t in types)
                {
                    tmpInspections = s.Inspections.FindAll(delegate(EInspection i) { return i.Result.InspectionType.Id == t.Id; });
                    if (tmpInspections.Count > 0)
                    {
                        tmpInspectionStructure = (EInspectionStructure) s.Clone();
                        tmpInspectionStructure.Inspections = tmpInspections;
                        matchingVisits.Add(tmpInspectionStructure);
                    }
                    
                }
            }
            return matchingVisits;
        }

        public static List<EInspectionStructure> MatchingResultType(List<EInspectionStructure> structures, List<EResultType> types)
        {
            List<EInspectionStructure> matchingVisits = new List<EInspectionStructure>();
            EInspectionStructure tmpInspectionStructure;
            List<EInspection> tmpInspections;
            foreach (EInspectionStructure s in structures)
            {
                foreach (EResultType t in types)
                {
                    tmpInspections = s.Inspections.FindAll(delegate(EInspection i) { return i.Result.Resultype.Id == t.Id; });
                    if (tmpInspections.Count > 0)
                    {
                        tmpInspectionStructure = (EInspectionStructure)s.Clone();
                        tmpInspectionStructure.Inspections = tmpInspections;
                        matchingVisits.Add(tmpInspectionStructure);
                    }
                }
            }
            return matchingVisits;
        }

        public static List<EInspectionStructure> MatchingProjects(List<EInspectionStructure> structures, List<EVendor_Project> projects)
        {
            List<EInspectionStructure> matchingVisits = new List<EInspectionStructure>();
            EInspection tmpInspection;
            foreach (EInspectionStructure s in structures)
            {
                foreach (EVendor_Project project in projects)
                {
                    tmpInspection = s.Inspections.Find(delegate(EInspection i) { return i.Project.Id == project.Id; });
                    if (tmpInspection != null)
                        matchingVisits.Add(s);
                }
            }
            return matchingVisits;
        }

        public static List<EInspectionStructure> MatchingIsElectrical(List<EInspectionStructure> structures, bool isElectrical)
        {
            List<EInspectionStructure> matchingVisits = new List<EInspectionStructure>();
            EInspectionStructure tmpInspectionStructure;
            List<EInspection> tmpInspections;
            foreach (EInspectionStructure s in structures)
            {
                tmpInspections = s.Inspections.FindAll(delegate(EInspection i) { return i.Result.IsElectrical == isElectrical; });
                if (tmpInspections.Count > 0)
                {
                    tmpInspectionStructure = (EInspectionStructure)s.Clone();
                    tmpInspectionStructure.Inspections = tmpInspections;
                    matchingVisits.Add(tmpInspectionStructure);
                }

            }
            return matchingVisits;
        }

        public static void SetAsset(EInspectionStructure structure, EUtility_Asset_XMissionStructure utility)
        {
            PPI.Client.Logic.Utility.LUtility_Asset_XMissionStructure.AssociateWithInspection(structure.Id, utility.Id);
        }

        public static bool NotAssignedPredicate(EInspectionStructure s)
        {
            if (s.Id == Int32.MinValue)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public static void Delete(Int32 structureId)
        {
            WebConsumer.Inspection.WInspection.Delete(structureId);
        }

        public static void DeleteWithPoles(Int32 structureId)
        {
            WebConsumer.Inspection.WInspection.DeleteWithPoles(structureId);
        }

        public static List<EInspectionStructure> LoadProjectStructuresByPole(Int32 poleId, string connectionString)
        {
            StringBuilder sql = new StringBuilder();

            #region Query

            sql.AppendLine("Select ");

            //Pole Fields
            sql.AppendLine("P.UtilityInspectionId as Pole_Id, ");
            sql.AppendLine("P.Latitude as Pole_Latitude, ");
            sql.AppendLine("P.Longitude as Pole_Longitude, ");
            sql.AppendLine("P.Year as Pole_Year, ");
            sql.AppendLine("P.LVisit as Pole_LVisit, ");
            sql.AppendLine("P.Number as Pole_Number, ");
            sql.AppendLine("P.AcquisitionDate as Pole_AcquisitionDate, ");

            sql.AppendLine("P.TreatmentFk as Pole_TreatmentFk, ");
            sql.AppendLine("TRE.LookupText as Pole_Treatment_Name, ");

            sql.AppendLine("P.ClassFk as Pole_ClassFk, ");
            sql.AppendLine("CLA.LookupText as Pole_Class_Name, ");

            sql.AppendLine("P.SpecieFk as Pole_SpecieFk, ");
            sql.AppendLine("E.LookupText as Pole_Specie_Name, ");

            sql.AppendLine("P.TopTypeFk as Pole_TopTypeFk, ");
            sql.AppendLine("T.Name as Pole_TopType_Name, ");

            sql.AppendLine("P.LengthFk as Pole_LengthFk, ");
            sql.AppendLine("L.Name as Pole_Length_Name, ");

            sql.AppendLine("P.InspectorFk as Pole_InspectorFk, ");
            sql.AppendLine("INSPEC.Name as Pole_Inspector_Name, ");

            sql.AppendLine("P.CompanyFk as Pole_CompanyFk, ");
            sql.AppendLine("C.Name as Pole_Company_Name, ");

            sql.AppendLine("P.MapFk as Pole_MapFk, ");
            sql.AppendLine("MA.Name as Pole_Map_Name, ");
            sql.AppendLine("P.SectionFk as Pole_Section, ");

            sql.AppendLine("P.ProjectFk as Pole_ProjectFk, ");
            sql.AppendLine("PR.Name as Pole_Project_Name, ");

            sql.AppendLine("P.ContactFk as Pole_ContactFk, ");
            sql.AppendLine("P.GuardsQuantity as GuardsQuantity, ");
            sql.AppendLine("P.LastCompany as LastCompany, ");
            sql.AppendLine("P.VisitTypeFk, ");
            sql.AppendLine("US.Name as Pole_Contact_Name, ");

            sql.AppendLine("V.VendorId as Pole_VendorFk, ");
            sql.AppendLine("V.Name as Pole_Vendor_Name, ");

            sql.AppendLine("P.Vendor_InspVisit_XMissionStructureFk as Pole_StructureFk, ");

            //Result Fields
            sql.AppendLine("R.UtilityInspectionResultId as Result_Id, ");
            sql.AppendLine("R.UtilityInspectionFk as Result_InspectionTypeFk, ");
            sql.AppendLine("I.Name as Result_InspectionType_Name, ");
            sql.AppendLine("R.InspectionTypeFk, ");
            sql.AppendLine("R.ResultTypeFk as Result_TypeFk, ");
            sql.AppendLine("Re.Name as Result_Type_Name, ");
            sql.AppendLine("R.Comments as Result_Comments, ");
            sql.AppendLine("R.bElectrical as Result_IsElectrical, ");

            //Structure Fields
            sql.AppendLine("S.Vendor_InspVisit_XMissionStructureId as Structure_Id, ");

            sql.AppendLine("V2.VendorId as Structure_VendorFk, ");
            sql.AppendLine("V2.Name as Structure_Vendor_Name, ");

            sql.AppendLine("S.Vendor_ProjectFk as Structure_ProjectFk, ");
            sql.AppendLine("PR2.Name as Structure_Project_Name, ");

            sql.AppendLine("S.Utility_Asset_XMisssionStructureFk as Structure_UtilityFk, ");

            sql.AppendLine("S.Vendor_Visit_TypeFK as Structure_VisitTypeFk, ");
            sql.AppendLine("VType.LookupText as Structure_VisitType_Name, ");
            sql.AppendLine("VType.LookupText as VisitType_Name, ");
            
            sql.AppendLine("S.Latitude as Structure_Latitude, ");
            sql.AppendLine("S.Longitude as Structure_Longitude, ");
            sql.AppendLine("S.Altitude as Structure_Altitude, ");
            sql.AppendLine("S.SatelliteCount as Structure_SatelliteCount, ");
            sql.AppendLine("S.SatelliteTime as Structure_SatelliteTime, ");
            sql.AppendLine("S.SolutionAvailable as Structure_SolutionAvailable, ");

            sql.AppendLine("S.Vendor_ContactFk as Structure_ContactFk, ");
            sql.AppendLine("US2.Name as Structure_Contact_Name, ");

            sql.AppendLine("S.Date as Structure_Date, ");
            sql.AppendLine("S.HandheldFK as Structure_HandheldFk, ");

            sql.AppendLine("S.XMissionStructures_TopTypeFk as Structure_TopTypeFk, ");
            sql.AppendLine("T2.Name as Structure_TopType_Name ");

            sql.AppendLine("From tb_UtilityInspections as P");
            sql.AppendLine("    INNER JOIN tb_UtilityInspectionResults R ON P.UtilityInspectionId = R.UtilityInspectionFk");
            sql.AppendLine("    LEFT JOIN tb_Vendor_InspVisit_XMissionStructures S ON P.Vendor_InspVisit_XMissionStructureFk = S.Vendor_InspVisit_XMissionStructureId");
            sql.AppendLine("    LEFT JOIN tb_InspectionTypes I ON R.InspectionTypeFk = I.InspectionTypeId");
            sql.AppendLine("    LEFT JOIN tb_ResultTypes Re ON R.ResultTypeFk= Re.ResultTypeId ");

            sql.AppendLine("    LEFT JOIN tb_Tops T ON P.ToptypeFk = T.TopId");
            sql.AppendLine("    LEFT JOIN tb_Tops T2 ON S.XMissionStructures_TopTypeFk = T2.TopId");
            sql.AppendLine("    LEFT JOIN tb_Companies C ON P.CompanyFk = C.CompanyId");
            sql.AppendLine("    LEFT JOIN tb_Inspectors INSPEC ON P.InspectorFk = INSPEC.InspectorId");
            sql.AppendLine("    LEFT JOIN tb_Lookups TRE ON (P.TreatmentFk = TRE.LookupValue AND TRE.LookupTypeFk = 'Treatments' )");
            sql.AppendLine("    LEFT JOIN tb_Lookups CLA ON (P.ClassFk = CLA.LookupValue AND CLA.LookupTypeFk = 'Classes' )");
            sql.AppendLine("    LEFT JOIN tb_Lookups E ON (P.SpecieFk = E.LookupValue AND E.LookupTypeFk = 'Species' )");
            sql.AppendLine("    LEFT JOIN tb_Maps MA ON P.MapFk = MA.MapId ");
            sql.AppendLine("    LEFT JOIN tb_Users US ON P.ContactFk = US.UserId ");
            sql.AppendLine("    LEFT JOIN tb_Users US2 ON S.Vendor_ContactFk = US2.UserId ");
            sql.AppendLine("    LEFT JOIN tb_Projects PR ON P.ProjectFk = PR.ProjectId ");
            sql.AppendLine("    LEFT JOIN tb_Projects PR2 ON S.Vendor_ProjectFk = PR2.ProjectId ");
            sql.AppendLine("    LEFT JOIN tb_Vendor V ON PR.VendorFk = V.VendorId ");
            sql.AppendLine("    LEFT JOIN tb_Vendor V2 ON PR2.VendorFk = V2.VendorId ");
            sql.AppendLine("    LEFT JOIN tb_Lookups as VType ON VType.LookupValue = S.Vendor_Visit_TypeFk AND VType.LookupTypeFk ='VisitTypes'");
            sql.AppendLine("    LEFT JOIN tb_Lengths L ON P.LengthFk = L.LengthId");
            sql.AppendLine(" Where (P.UtilityInspectionId = @PoleId)");
            sql.AppendLine("Order by Vendor_InspVisit_XMissionStructureId");




            #endregion

            List<SqlParameter> listParameters = new List<SqlParameter>();
            listParameters.Add(DBHelper.CreateIntParameter("@PoleId", ParameterDirection.Input, false, poleId));

            DataTable dtStructures = LMobileDatabase.ExecuteQueryDataset(sql.ToString(), connectionString, listParameters.ToArray()).Tables[0];

            List<EInspectionStructure> structures = ParseDatatableStructures(dtStructures, connectionString);
            return structures;

        }

        public static List<EInspectionStructure> LoadProjectStructuresByAsset(Int32 assetId, string connectionString)
        {
            StringBuilder sql = new StringBuilder();

            #region Query

            sql.AppendLine("Select ");

            //Pole Fields
            sql.AppendLine("P.UtilityInspectionId as Pole_Id, ");
            sql.AppendLine("P.Latitude as Pole_Latitude, ");
            sql.AppendLine("P.Longitude as Pole_Longitude, ");
            sql.AppendLine("P.Year as Pole_Year, ");
            sql.AppendLine("P.LVisit as Pole_LVisit, ");
            sql.AppendLine("P.Number as Pole_Number, ");
            sql.AppendLine("P.AcquisitionDate as Pole_AcquisitionDate, ");

            sql.AppendLine("P.TreatmentFk as Pole_TreatmentFk, ");
            sql.AppendLine("TRE.LookupText as Pole_Treatment_Name, ");

            sql.AppendLine("P.ClassFk as Pole_ClassFk, ");
            sql.AppendLine("CLA.LookupText as Pole_Class_Name, ");

            sql.AppendLine("P.SpecieFk as Pole_SpecieFk, ");
            sql.AppendLine("E.LookupText as Pole_Specie_Name, ");

            sql.AppendLine("P.TopTypeFk as Pole_TopTypeFk, ");
            sql.AppendLine("T.Name as Pole_TopType_Name, ");

            sql.AppendLine("P.LengthFk as Pole_LengthFk, ");
            sql.AppendLine("L.Name as Pole_Length_Name, ");

            sql.AppendLine("P.InspectorFk as Pole_InspectorFk, ");
            sql.AppendLine("INSPEC.Name as Pole_Inspector_Name, ");

            sql.AppendLine("P.CompanyFk as Pole_CompanyFk, ");
            sql.AppendLine("C.Name as Pole_Company_Name, ");

            sql.AppendLine("P.MapFk as Pole_MapFk, ");
            sql.AppendLine("MA.Name as Pole_Map_Name, ");
            sql.AppendLine("P.SectionFk as Pole_Section, ");

            sql.AppendLine("P.ProjectFk as Pole_ProjectFk, ");
            sql.AppendLine("PR.Name as Pole_Project_Name, ");

            sql.AppendLine("P.ContactFk as Pole_ContactFk, ");
            sql.AppendLine("US.Name as Pole_Contact_Name, ");

            sql.AppendLine("V.VendorId as Pole_VendorFk, ");
            sql.AppendLine("V.Name as Pole_Vendor_Name, ");

            sql.AppendLine("P.Vendor_InspVisit_XMissionStructureFk as Pole_StructureFk, ");

            //Result Fields
            sql.AppendLine("R.UtilityInspectionResultId as Result_Id, ");
            sql.AppendLine("R.UtilityInspectionFk as Result_InspectionTypeFk, ");
            sql.AppendLine("I.Name as Result_InspectionType_Name, ");
            sql.AppendLine("R.InspectionTypeFk, ");
            sql.AppendLine("R.ResultTypeFk as Result_TypeFk, ");
            sql.AppendLine("Re.Name as Result_Type_Name, ");
            sql.AppendLine("R.Comments as Result_Comments, ");
            sql.AppendLine("R.bElectrical as Result_IsElectrical, ");

            //Structure Fields
            sql.AppendLine("S.Vendor_InspVisit_XMissionStructureId as Structure_Id, ");

            sql.AppendLine("V2.VendorId as Structure_VendorFk, ");
            sql.AppendLine("V2.Name as Structure_Vendor_Name, ");

            sql.AppendLine("S.Vendor_ProjectFk as Structure_ProjectFk, ");
            sql.AppendLine("PR2.Name as Structure_Project_Name, ");

            sql.AppendLine("S.Utility_Asset_XMisssionStructureFk as Structure_UtilityFk, ");

            sql.AppendLine("S.Vendor_Visit_TypeFK as Structure_VisitTypeFk, ");
            sql.AppendLine("VType.LookupText as Structure_VisitType_Name, ");


            sql.AppendLine("S.Latitude as Structure_Latitude, ");
            sql.AppendLine("S.Longitude as Structure_Longitude, ");
            sql.AppendLine("S.Altitude as Structure_Altitude, ");
            sql.AppendLine("S.SatelliteCount as Structure_SatelliteCount, ");
            sql.AppendLine("S.SatelliteTime as Structure_SatelliteTime, ");
            sql.AppendLine("S.SolutionAvailable as Structure_SolutionAvailable, ");

            sql.AppendLine("S.Vendor_ContactFk as Structure_ContactFk, ");
            sql.AppendLine("US2.Name as Structure_Contact_Name, ");

            sql.AppendLine("S.Date as Structure_Date, ");
            sql.AppendLine("S.HandheldFK as Structure_HandheldFk, ");

            sql.AppendLine("S.XMissionStructures_TopTypeFk as Structure_TopTypeFk, ");
            sql.AppendLine("T2.Name as Structure_TopType_Name, ");

            sql.AppendLine("P.GuardsQuantity as GuardsQuantity, ");
            sql.AppendLine("P.LastCompany as LastCompany, ");
            sql.AppendLine("P.VisitTypeFk, ");
            sql.AppendLine("VT.LookupText as VisitType_Name  ");


            sql.AppendLine("From tb_UtilityInspections as P");
            sql.AppendLine("    INNER JOIN tb_UtilityInspectionResults R ON P.UtilityInspectionId = R.UtilityInspectionFk");
            sql.AppendLine("    LEFT JOIN tb_Vendor_InspVisit_XMissionStructures S ON P.Vendor_InspVisit_XMissionStructureFk = S.Vendor_InspVisit_XMissionStructureId");
            sql.AppendLine("    LEFT JOIN tb_InspectionTypes I ON R.InspectionTypeFk = I.InspectionTypeId");
            sql.AppendLine("    LEFT JOIN tb_ResultTypes Re ON R.ResultTypeFk= Re.ResultTypeId ");

            sql.AppendLine("    LEFT JOIN tb_Tops T ON P.ToptypeFk = T.TopId");
            sql.AppendLine("    LEFT JOIN tb_Tops T2 ON S.XMissionStructures_TopTypeFk = T2.TopId");
            sql.AppendLine("    LEFT JOIN tb_Companies C ON P.CompanyFk = C.CompanyId");
            sql.AppendLine("    LEFT JOIN tb_Inspectors INSPEC ON P.InspectorFk = INSPEC.InspectorId");
            sql.AppendLine("    LEFT JOIN tb_Lookups TRE ON (P.TreatmentFk = TRE.LookupValue AND TRE.LookupTypeFk = 'Treatments' )");
            sql.AppendLine("    LEFT JOIN tb_Lookups CLA ON (P.ClassFk = CLA.LookupValue AND CLA.LookupTypeFk = 'Classes' )");
            sql.AppendLine("    LEFT JOIN tb_Lookups E ON (P.SpecieFk = E.LookupValue AND E.LookupTypeFk = 'Species' )");
            sql.AppendLine("    LEFT JOIN tb_Maps MA ON P.MapFk = MA.MapId ");
            sql.AppendLine("    LEFT JOIN tb_Users US ON P.ContactFk = US.UserId ");
            sql.AppendLine("    LEFT JOIN tb_Users US2 ON S.Vendor_ContactFk = US2.UserId ");
            sql.AppendLine("    LEFT JOIN tb_Projects PR ON P.ProjectFk = PR.ProjectId ");
            sql.AppendLine("    LEFT JOIN tb_Projects PR2 ON S.Vendor_ProjectFk = PR2.ProjectId ");
            sql.AppendLine("    LEFT JOIN tb_Vendor V ON PR.VendorFk = V.VendorId ");
            sql.AppendLine("    LEFT JOIN tb_Vendor V2 ON PR2.VendorFk = V2.VendorId ");
            sql.AppendLine("    LEFT JOIN tb_Lookups as VType ON VType.LookupValue = S.Vendor_Visit_TypeFk AND VType.LookupTypeFk ='VisitTypes'");
            sql.AppendLine("    LEFT JOIN tb_Lengths L ON P.LengthFk = L.LengthId");
            sql.AppendLine("    LEFT JOIN tb_Lookups as VT ON VT.LookupValue = P.VisitTypeFk AND VT.LookupTypeFk ='VisitTypes'");
            sql.AppendLine(" Where ( @AssetId is null OR S.Utility_Asset_XMisssionStructureFk = @AssetId)");
            sql.AppendLine("Order by Vendor_InspVisit_XMissionStructureId");


            #endregion

            List<SqlParameter> listParameters = new List<SqlParameter>();
            
            if(assetId == Int32.MinValue)
                listParameters.Add(DBHelper.CreateIntParameter("@AssetId", ParameterDirection.Input, true, DBNull.Value));
            else
                listParameters.Add(DBHelper.CreateIntParameter("@AssetId", ParameterDirection.Input, false, assetId));

            DataTable dtStructures = LMobileDatabase.ExecuteQueryDataset(sql.ToString(), connectionString, listParameters.ToArray()).Tables[0];

            List<EInspectionStructure> structures = ParseDatatableStructures(dtStructures, connectionString);
            return structures;

        }

    }
}
